package ar.pegasus.clasificador.dao.imp;

import java.util.List;

import javax.ejb.Stateless;

import ar.pegasus.clasificador.dao.api.local.CalleDAOLocal;
import ar.pegasus.clasificador.entidades.Calle;
import ar.pegasus.clasificador.entidades.Seccion;
import ar.pegasus.clasificador.entidades.TramoCalle;
import ar.pegasus.clasificador.entidades.wrappers.CalleWrapper;
import ar.pegasus.clasificador.entidades.wrappers.InterseccionWrapper;
import ar.pegasus.clasificador.entidades.wrappers.TramoCalleWrapper;
import ar.pegasus.framework.dao.api.impl.GenericDAO;

@Stateless
public class CalleDAO extends GenericDAO<Calle, Integer> implements CalleDAOLocal {

	@Override
	public boolean chequearCalle(Seccion seccion, String andcond, String andend) {
		String Sqlstatement =  "SELECT seccion, codigo, nombre " +
							   "  FROM v_calle WHERE ( seccion = " + seccion.getIdSeccion() + " ) " +
							   andcond + " " + andend +   
							   "UNION SELECT seccion, codigo, sinonimo as nombre " +
							   "FROM v_sinonimo_calle WHERE ( seccion = " + seccion.getIdSeccion() + " ) " +
							   andcond + " " + andend;
		@SuppressWarnings("unchecked")
		List<CalleWrapper> calles = getEntityManager().createNativeQuery(Sqlstatement).getResultList();
		return !calles.isEmpty();
	}

	@Override
	public List<CalleWrapper> getCalles(Seccion seccion, String andcond, String andend) {
		String Sqlstatement =  "SELECT seccion, codigo, nombre " +
		   "  FROM v_calle WHERE ( seccion = " + seccion.getIdSeccion() + " ) " +
		   andcond + " " + andend +   
		   "UNION SELECT seccion, codigo, sinonimo as nombre " +
		   "FROM v_sinonimo_calle WHERE ( seccion = " + seccion.getIdSeccion() + " ) " +
		   andcond + " " + andend;
		@SuppressWarnings("unchecked")
		List<CalleWrapper> calles = getEntityManager().createNativeQuery(Sqlstatement).getResultList();
		return calles;
	}


	@Override
	public List<TramoCalle> getTramosCalleByIdCalle(Seccion seccion, long idCalle) {
		String sql = " FROM TramoCalle tc " +
				     "WHERE tc.seccion.idSeccion = :idSeccion " +
				     "  AND tc.calle.idCalle = :idCalle" +
				     "ORDER BY tc.inicio ASC, tc.circuito ";
		@SuppressWarnings("unchecked")
		List<TramoCalle> tramos = getEntityManager().createQuery(sql).setParameter("idSeccion", seccion.getIdSeccion()).setParameter("idCalle", idCalle).getResultList();
		return tramos;
	}

	@Override
	public List<TramoCalleWrapper> getTramosCalleConLocalidadAltura(int idSeccion, long idCalle, long alturaCalle, String paridad) {
		String sql ="SELECT c.nombre, " +
					"		l.idLocalidad," +
					"		l.f_idLocalidadPrincipal," +
					"		tc.inicio," +
					"		tc.fin," +
					" 		tc.criterio," +
					" 		tc.circuito" +
					"  FROM localidad l," +
					"		tramoCalle tc," +
					"		calle  c" +
					" WHERE tc.f_idLocalidad = l.idLocalidad" +
					"   AND c.f_idSeccion = tc.f_idSeccion" +
					"   AND c.idCalle = tc.f_idCalle" +
					"   AND ( tc.f_idSeccion = :idSeccion" +
					"       AND c.idCalle = :idCalle" +
					"       AND :alturaCalle BETWEEN tc.inicio AND tc.fin" +
					"       AND ( tc.criterio IN (:paridad,'N') ) )";
		@SuppressWarnings("unchecked")
		List<TramoCalleWrapper> calles = getEntityManager().createNativeQuery(sql)
															.setParameter("idSeccion", idSeccion)
															.setParameter("idCalle", idCalle)
															.setParameter("alturaCalle", alturaCalle)
															.setParameter("paridad", paridad)
															.getResultList();
		return calles;
	}

	@Override
	public List<TramoCalleWrapper> getTramosCalle(int idSeccion, int idCalle) {
		String sql ="SELECT c.nombre,    " +
					"  	    l.idLocalidad,  " + 
					"	    l.f_idLocalidadPrincipal,  " + 
					"		tc.inicio," +
					"		tc.fin," +
					" 		tc.criterio," +
					" 		tc.circuito" +
					"  FROM Calle c,   " +
					"       Localidad l,   " +
					"       TramoCalle tc " +
					" WHERE c.f_idseccion = l.f_idseccion" + 
				    "   AND tc.f_idlocalidad = l.idlocalidad" +
				    "   AND tc.f_idseccion = c.f_idseccion" +
				    "   AND	tc.f_idcalle = c.idcalle  " +
				    "   AND ( c.f_idseccion = :seccion " +
				    "     AND c.idcalle = :idCalle )" +
					"ORDER BY tc.inicio ASC"; 
		@SuppressWarnings("unchecked")
		List<TramoCalleWrapper> calles = getEntityManager().createNativeQuery(sql)
															.setParameter("idSeccion", idSeccion)
															.setParameter("idCalle", idCalle)
															.getResultList();
		return calles;
	}

	@Override
	public List<InterseccionWrapper> getInterseccionesPorLocalidad(int idSeccion, int idCallePrincipal, int idCalleSecundaria) {
		String sql = "SELECT tramo_calle_a.f_idLocalidad, " + 
					 "       tramo_calle_a.circuito " + 
					 "  FROM interseccion i,   " + 
					 "       tramoCalle tramo_calle_a," +    
					 "       tramoCalle tramo_calle_b  " + 
					 " WHERE i.f_idSeccion = tramo_calle_a.f_idSeccion " + 
					 "   AND i.f_idSeccion = tramo_calle_b.f_idSeccion " + 
					 "   AND i.f_idCallePrincipal = tramo_calle_a.f_idCalle " +
					 "   AND i.f_idCalleCruce = tramo_calle_b.f_idCalle " +
					 "   AND ( i.f_idSeccion = :idSeccion   " +
					 "         AND i.f_idCallePrincipal = :idCallePrincipal " + 
					 "         AND i.f_idCalleCruce     = :idCalleSecundaria  " +
					 "         AND i.alturaPrincipal BETWEEN tramo_calle_a.inicio AND tramo_calle_a.fin " + 
					 "         AND i.alturaCruce BETWEEN tramo_calle_b.inicio AND tramo_calle_b.fin  " +
					 "         AND tramo_calle_a.circuito = tramo_calle_b.circuito  " +
					 "         AND tramo_calle_a.f_idLocalidad = tramo_calle_b.f_idLocalidad " + 	
					 "		  )";
		@SuppressWarnings("unchecked")
		List<InterseccionWrapper> intersecciones = getEntityManager().createNativeQuery(sql)
															.setParameter("idSeccion", idSeccion)
															.setParameter("idCallePrincipal", idCallePrincipal)
															.setParameter("idCalleSecundaria", idCalleSecundaria)
															.getResultList();
		return intersecciones;
	}

	@Override
	public List<InterseccionWrapper> getIntersecciones(int idSeccion, int idCallePrincipal, int idCalleSecundaria) {
		String sql = "SELECT tramo_calle_a.f_idLocalidad, " + 
					 "       tramo_calle_a.circuito " + 
					 "  FROM interseccion i,   " + 
					 "       tramoCalle tramo_calle_a," +    
					 "       tramoCalle tramo_calle_b  " + 
					 " WHERE i.f_idSeccion = tramo_calle_a.f_idSeccion " + 
					 "   AND i.f_idSeccion = tramo_calle_b.f_idSeccion " + 
					 "   AND i.f_idCallePrincipal = tramo_calle_a.f_idCalle " +
					 "   AND i.f_idCalleCruce = tramo_calle_b.f_idCalle " +
					 "   AND i.f_idSeccion = :idSeccion   " +
					 "   AND i.f_idCallePrincipal = :idCallePrincipal " + 
					 "   AND i.f_idCalleCruce     = :idCalleSecundaria  " +
					 "   AND i.alturaPrincipal BETWEEN tramo_calle_a.inicio AND tramo_calle_a.fin " + 
					 "   AND i.alturaCruce BETWEEN tramo_calle_b.inicio AND tramo_calle_b.fin  " +
					 "   AND tramo_calle_a.circuito = tramo_calle_b.circuito  ";
		
			@SuppressWarnings("unchecked")
			List<InterseccionWrapper> intersecciones = getEntityManager().createNativeQuery(sql)
															.setParameter("idSeccion", idSeccion)
															.setParameter("idCallePrincipal", idCallePrincipal)
															.setParameter("idCalleSecundaria", idCalleSecundaria)
															.getResultList();
			return intersecciones;
	}

	@Override
	public List<InterseccionWrapper> getEntreCallesPorLocalidad(int idSeccion, int idCallePrincipal, int idCalleSecundaria, int idCalleTerciaria) {
		String sql = "SELECT tramo_calle_a.circuito, " + 
				     "       tramo_calle_a.f_idLocalidad " +
					 "  FROM tramoCalle tramo_calle_a,    " +
					 "       tramoCalle tramo_calle_b,    " +
					 "       tramoCalle tramo_calle_c,    " +
					 "       tramoCalle tramo_calle_d,    " +
					 "       interseccion interseccion_a, " +   
					 "       interseccion interseccion_b  " + 
					 " WHERE tramo_calle_a.f_idSeccion = interseccion_a.f_idSeccion " +
				     "   AND tramo_calle_b.f_idSeccion = interseccion_a.f_idSeccion " +
				     "   AND tramo_calle_c.f_idSeccion = interseccion_b.f_idSeccion " + 
				     "   AND interseccion_b.f_idSeccion = tramo_calle_d.f_idSeccion " +
				     "   AND tramo_calle_a.f_idCalle = interseccion_a.f_idCallePrincipal " + 
				     "   AND tramo_calle_b.f_idCalle = interseccion_a.f_idCalleCruce " +
				     "   AND tramo_calle_c.f_idCalle = interseccion_b.f_idCallePrincipal " + 
				     "   AND tramo_calle_d.f_idCalle = interseccion_b.f_idCalleCruce " + 
					 "   AND interseccion_a.f_idCallePrincipal = interseccion_b.f_idCallePrincipal " + 
				     "   AND tramo_calle_a.circuito = tramo_calle_b.circuito " + 
				     "   AND tramo_calle_c.circuito = tramo_calle_d.circuito " + 
				     "   AND tramo_calle_c.circuito = tramo_calle_a.circuito " + 
				     "   AND tramo_calle_a.f_idLocalidad = tramo_calle_b.f_idLocalidad " + 
				     "   AND tramo_calle_c.f_idLocalidad = tramo_calle_d.f_idLocalidad " + 
				     "   AND tramo_calle_c.f_idLocalidad = tramo_calle_a.f_idLocalidad " + 
				     "   AND ( interseccion_a.f_idSeccion = :idSeccion " + 
				     "     AND interseccion_a.f_idCallePrincipal = :idCallePrincipal " + 
				     "     AND interseccion_a.f_idCalleCruce = :idCalleSecundaria " + 
				     "     AND interseccion_b.f_idCalleCruce = :idCalleTerciaria ) " + 
				     "   AND interseccion_a.alturaPrincipal BETWEEN tramo_calle_a.inicio AND  tramo_calle_a.fin " + 
				     "   AND interseccion_b.alturaPrincipal BETWEEN tramo_calle_c.inicio AND  tramo_calle_c.fin " + 
				     "   AND interseccion_a.alturaCruce BETWEEN tramo_calle_b.inicio AND tramo_calle_b.fin " + 
				     "   AND interseccion_b.alturaCruce BETWEEN tramo_calle_d.inicio AND tramo_calle_d.fin";
						
		@SuppressWarnings("unchecked")
		List<InterseccionWrapper> intersecciones = getEntityManager().createNativeQuery(sql)
															.setParameter("idSeccion", idSeccion)
															.setParameter("idCallePrincipal", idCallePrincipal)
															.setParameter("idCalleSecundaria", idCalleSecundaria)
															.setParameter("idCalleTerciaria", idCalleTerciaria)
															.getResultList();
		return intersecciones;
	}
	
	@Override
	public List<InterseccionWrapper> getEntreCalles(int idSeccion, int idCallePrincipal, int idCalleSecundaria, int idCalleTerciaria) {
		String sql = "SELECT tramo_calle_a.f_idLocalidad, " +
					 "       tramo_calle_a.circuito       " + 
					 "  FROM tramoCalle tramo_calle_a,    " +
					 "       tramoCalle tramo_calle_b,    " +
					 "       tramoCalle tramo_calle_c,    " +
					 "       tramoCalle tramo_calle_d,    " +
					 "       interseccion interseccion_a, " +   
					 "       interseccion interseccion_b  " + 
					 " WHERE tramo_calle_a.f_idSeccion = interseccion_a.f_idSeccion " +
				     "   AND tramo_calle_b.f_idSeccion = interseccion_a.f_idSeccion " +
				     "   AND tramo_calle_c.f_idSeccion = interseccion_b.f_idSeccion " + 
				     "   AND tramo_calle_d.f_idSeccion = interseccion_b.f_idSeccion " +
				     "   AND tramo_calle_a.f_idCalle = interseccion_a.f_idCallePrincipal " + 
				     "   AND tramo_calle_b.f_idCalle = interseccion_a.f_idCalleCruce " +
				     "   AND tramo_calle_c.f_idCalle = interseccion_b.f_idCallePrincipal " + 
				     "   AND tramo_calle_d.f_idCalle = interseccion_b.f_idCalleCruce " + 
					 "   AND interseccion_a.f_idCallePrincipal = interseccion_b.f_idCallePrincipal " + 
				     "   AND tramo_calle_a.circuito = tramo_calle_b.circuito " + 
				     "   AND tramo_calle_c.circuito = tramo_calle_d.circuito " + 
				     "   AND tramo_calle_c.circuito = tramo_calle_a.circuito " + 
				     "   AND interseccion_a.f_idSeccion = :idSeccion " + 
				     "   AND interseccion_a.f_idCallePrincipal = :idCallePrincipal " + 
				     "   AND interseccion_a.f_idCalleCruce = :idCalleSecundaria " + 
				     "   AND interseccion_b.f_idCalleCruce = :idCalleTerciaria  " + 
				     "   AND interseccion_a.alturaPrincipal BETWEEN tramo_calle_a.inicio AND  tramo_calle_a.fin " + 
				     "   AND interseccion_b.alturaPrincipal BETWEEN tramo_calle_c.inicio AND  tramo_calle_c.fin " + 
				     "   AND interseccion_a.alturaCruce BETWEEN tramo_calle_b.inicio AND tramo_calle_b.fin " + 
				     "   AND interseccion_b.alturaCruce BETWEEN tramo_calle_d.inicio AND tramo_calle_d.fin";

			@SuppressWarnings("unchecked")
			List<InterseccionWrapper> intersecciones = getEntityManager().createNativeQuery(sql)
															.setParameter("idSeccion", idSeccion)
															.setParameter("idCallePrincipal", idCallePrincipal)
															.setParameter("idCalleSecundaria", idCalleSecundaria)
															.setParameter("idCalleTerciaria", idCalleTerciaria)
															.getResultList();
			return intersecciones;

	}


	@Override
	public List<TramoCalleWrapper> getTramosCalleConAltura(int idSeccion, int idCalle, long alturaCalle, String paridad) {
		String sql ="SELECT c.nombre, " +
					"		tc.f_idLocalidad," +
					"		0," +
					"		tc.inicio," +
					"		tc.fin," +
					" 		tc.criterio," +
					" 		tc.circuito" +
					"  FROM tramoCalle tc," +
					"		calle  c" +
					" WHERE c.f_idSeccion  = tc.f_idSeccion" +
					"   AND c.idCalle      = tc.f_idCalle" +
					"   AND tc.f_idSeccion = :idSeccion" +
					"   AND c.idCalle      = :idCalle" +
					"   AND :alturaCalle BETWEEN tc.inicio AND tc.fin" +
					"   AND tc.criterio IN (:paridad,'N')" +
					"ORDER BY tc.inicio DESC, tc.fin ASC";
					
		@SuppressWarnings("unchecked")
		List<TramoCalleWrapper> tramos = getEntityManager().createNativeQuery(sql)
														.setParameter("idSeccion", idSeccion)
														.setParameter("idCalle", idCalle)
														.setParameter("alturaCalle", alturaCalle)
														.setParameter("paridad", paridad)
														.getResultList();
		return tramos;
	}

}
